{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "50384686",
   "metadata": {},
   "source": [
    "# 找到页面的数据API接口\n",
    "提供正确的用户请求酬载（payload）\n",
    "准备请求的headers，增加cookie信息（用户登录之后的cookie），保证数据的合理性"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d7f86c22",
   "metadata": {},
   "outputs": [],
   "source": [
    "用户输入职位 = input(\"请输入你要查询的职位：\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e8c2409",
   "metadata": {},
   "outputs": [],
   "source": [
    "城市编码 = {\n",
    "    '北京':'010',\n",
    "    '上海':'020',\n",
    "    '广州':'050020',\n",
    "    '深圳':'050090',\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "46d7bd40",
   "metadata": {},
   "outputs": [],
   "source": [
    "用户输入城市 = 城市编码[input(\"请输入你要查询的城市：\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1fb1b731",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import json\n",
    "\n",
    "url = \"https://apic.liepin.com/api/com.liepin.searchfront4c.pc-search-job\"\n",
    "payload = {\n",
    "    \"data\": {\n",
    "        \"mainSearchPcConditionForm\": {\n",
    "            \"city\": 城市编码[用户输入城市],\n",
    "            \"dq\": 城市编码[用户输入城市],\n",
    "            \"pubTime\": \"\",\n",
    "            \"currentPage\": 0,\n",
    "            \"pageSize\": 40,\n",
    "            \"key\": 用户输入职位,\n",
    "            \"suggestTag\": \"\",\n",
    "            \"workYearCode\": \"0\",\n",
    "            \"compId\": \"\",\n",
    "            \"compName\": \"\",\n",
    "            \"compTag\": \"\",\n",
    "            \"industry\": \"\",\n",
    "            \"salary\": \"\",\n",
    "            \"jobKind\": \"\",\n",
    "            \"compScale\": \"\",\n",
    "            \"compKind\": \"\",\n",
    "            \"compStage\": \"\",\n",
    "            \"eduLevel\": \"\"\n",
    "        },\n",
    "        \"passThroughForm\": {\n",
    "            \"scene\": \"input\",\n",
    "            \"skId\": \"j94obslcmjbab56awz8oyuhbtz0jn5q8\",\n",
    "            \"fkId\": \"r1ojcsqlie41f73xlfie7ndgyjpol038\",\n",
    "            \"ckId\": \"dk529rz9zy5gro8ce7hnlrgvl5ezkmfa\",\n",
    "            \"suggest\": None\n",
    "        }\n",
    "    }\n",
    "}\n",
    "\n",
    "# set the headers\n",
    "headers = {\n",
    "    'Accept': 'application/json, text/plain, */*',\n",
    "    'Accept-Encoding': 'gzip, deflate, br',\n",
    "    'Accept-Language': 'zh-CN,zh;q=0.9',\n",
    "    'Cache-Control': 'no-cache',\n",
    "    'Connection': 'keep-alive',\n",
    "    'Content-Length': '412',\n",
    "    'Content-Type': 'application/json;charset=UTF-8;',\n",
    "    'Cookie':'inited_user=7143dbfc8d832652823bd64b1481ca3c; __gc_id=de54ea2e47654f2d9731240061698a42; _ga=GA1.1.1674171972.1681903465; __uuid=1681903464951.45; need_bind_tel=false; c_flag=553813100ff971d55ff0a28712e6c5f4; imClientId=b5e1273d5cb093f4551d85b2fb739565; imId=b5e1273d5cb093f4ff8353f2429edccb; imClientId_0=b5e1273d5cb093f4551d85b2fb739565; imId_0=b5e1273d5cb093f4ff8353f2429edccb; new_user=false; XSRF-TOKEN=Tg12FUSPR_iNbLrTMw-lCQ; Hm_lvt_a2647413544f5a04f00da7eee0d5e200=1685531411,1686139009; __tlog=1686139125940.16%7C00000000%7C00000000%7C00000000%7C00000000; acw_tc=ac11000116861391263855868e00cff80d0e49265d9fbc6ff0db3ce6959869; UniqueKey=92e8ad0c82debad041344312b1f2ca2b; liepin_login_valid=0; lt_auth=7%2B8PPnADyF765CXb2GBd4vtKht2oWGWc8HtYhEoAitK%2BWfax4P%2FmQAOHqrUA%2BioIqxkjfvUzMLb2M%2Bn9wHJM6UIW%2FVGkk564t%2FO%2B1HYKTuJnJ%2Faih6qomZ3UFJwvwS8Kn3k2oy9Pyhnwthcbcr768ErIs5jW17yc8cvMsxe%2FgTEVWA%3D%3D; access_system=C; user_roles=0; user_photo=5f8fa3bc8dbe6273dcf85e5e08u.png; user_name=%E6%A2%81%E9%A2%96; inited_user=7143dbfc8d832652823bd64b1481ca3c; imApp_0=1; Hm_lpvt_a2647413544f5a04f00da7eee0d5e200=1686139487; __session_seq=6; __uv_seq=6; fe_im_socketSequence_new_0=3_3_3; fe_im_opened_pages=; fe_im_connectJson_0=%7B%220_92e8ad0c82debad041344312b1f2ca2b%22%3A%7B%22socketConnect%22%3A%222%22%2C%22connectDomain%22%3A%22liepin.com%22%7D%7D; _ga_54YTJKWN86=GS1.1.1686139006.7.1.1686139507.0.0.0',\n",
    "    'Host': 'apic.liepin.com',\n",
    "    'Origin': 'https://www.liepin.com',\n",
    "    'Pragma': 'no-cache',\n",
    "    'Referer': 'https://www.liepin.com/',\n",
    "    'sec-ch-ua': '\"Google Chrome\";v=\"111\", \"Not(A:Brand\";v=\"8\", \"Chromium\";v=\"111\"',\n",
    "    'sec-ch-ua-mobile': '?0',\n",
    "    'sec-ch-ua-platform': '\"macOS\"',\n",
    "    'Sec-Fetch-Dest': 'empty',\n",
    "    'Sec-Fetch-Mode': 'cors',\n",
    "    'Sec-Fetch-Site': 'same-site',\n",
    "    'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/113.0.0.0 Safari/537.36',\n",
    "    'X-Client-Type': 'web',\n",
    "    'X-Fscp-Bi-Stat': '{\"location\": \"https://www.liepin.com/zhaopin/?inputFrom=www_index&workYearCode=0&key=%E4%BA%A7%E5%93%81%E7%BB%8F%E7%90%86&scene=input&ckId=htihov8m2frxgy6ywo2wsg2gncnydzlb&dq=\"}',\n",
    "    'X-Fscp-Fe-Version': '',\n",
    "    'X-Fscp-Std-Info': '{\"client_id\": \"40108\"}',\n",
    "    'X-Fscp-Trace-Id': 'fea335b6-f4a4-42fd-9bd8-6fe41ffec413',\n",
    "    'X-Fscp-Version': '1.1',\n",
    "    'X-Requested-With': 'XMLHttpRequest',\n",
    "    'X-XSRF-TOKEN': 'XMz5EHIASaeNsiKARaDj0g'\n",
    "}\n",
    "\n",
    "# send a POST request with headers\n",
    "r = requests.post(url, data=json.dumps(payload), headers=headers)\n",
    "\n",
    "# extract the JSON data from the response\n",
    "response_data = r.json()\n",
    "\n",
    "# example: print the number of job postings returned\n",
    "print(response_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "10b57553",
   "metadata": {},
   "outputs": [],
   "source": [
    "page = response_data['data']['pagination']['totalPage']\n",
    "page"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65ce57ab",
   "metadata": {},
   "source": [
    "# 翻页"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "be65d5d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "response_df = []\n",
    "for i in range(page): # 需要判断页面的数据有多少页\n",
    "    payload['data']['mainSearchPcConditionForm']['currentPage'] = i\n",
    "    # send a POST request with headers\n",
    "    r = requests.post(url, data=json.dumps(payload), headers=headers)\n",
    "\n",
    "    # extract the JSON data from the response\n",
    "    response_data = r.json()\n",
    "    print(response_data)\n",
    "    df = pd.json_normalize(response_data['data']['data']['jobCardList'])\n",
    "    response_df.append(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "da7d8344",
   "metadata": {},
   "outputs": [],
   "source": [
    "response_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4645ac2",
   "metadata": {},
   "source": [
    "# 整理数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f71f4a10",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat(response_df)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03650a0e",
   "metadata": {},
   "source": [
    "# 数据储存"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb16ca1e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "08678077",
   "metadata": {},
   "outputs": [],
   "source": [
    "time.localtime()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "048be935",
   "metadata": {},
   "outputs": [],
   "source": [
    "output_time = str(time.localtime().tm_mon)\\\n",
    "             +str(time.localtime().tm_mday)+'_'\\\n",
    "             +str(time.localtime().tm_hour)\\\n",
    "             +str(time.localtime().tm_min)\\\n",
    "output_time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e5d570e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 按照职位名称和时间导出文件\n",
    "df.to_excel(key+'_liepin_'+key+'.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b45261a4",
   "metadata": {},
   "source": [
    "# 数据分析"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7eb9ef8b",
   "metadata": {},
   "source": [
    "1.Pandas/Numpy\n",
    "2.Pyecharts(bokeh、matplollab、seaborn、echarts、Tebleau)更考虑用户体验"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "98c0898f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "response"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9a5d7e2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel('liepin_'+key+output_time+'.xlsx')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45f6f505",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f84da83",
   "metadata": {},
   "source": [
    "# 筛选数据列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d565ada2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_PM_sh =  df[['job.labels','job.refreshTime','job.title','job.salary','job.dq','job.topJob','job.requireWorkYears','job.requireEduLevel','comp.compStage','comp.compName','comp.compIndustry','comp.compScale']]\n",
    "df_PM_sh"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68b7df0a",
   "metadata": {},
   "source": [
    "# PM分布"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33a122c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_PM_sh['job.dq'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61532b93",
   "metadata": {},
   "outputs": [],
   "source": [
    "上海地区 = [  i.split('-')[1]       for i in df_PM_sh['job.dq'].value_counts().index.tolist()[1:]]\n",
    "上海地区"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba4ad31e",
   "metadata": {},
   "outputs": [],
   "source": [
    "地区 = [df_PM_gz['job.dq'].value_counts().index.tolist()[i].split('-')[1]\\\n",
    "     for i,v in enumerate(df_PM_gz['job.dq'].dvalue_counts().index.tolist()) if '-' in v]\n",
    "地区"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "10922d21",
   "metadata": {},
   "outputs": [],
   "source": [
    "上海_岗位个数 = df_PM_sh['job.dq'].values_counts().values.tolist()[1:]\n",
    "上海_岗位个数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0a0251d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install pycharts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a3b1f038",
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install pycharts\n",
    "# 可视化：以可视化工具数据形态符合的数据进行输入\n",
    "\n",
    "from pyecharts import options as opts\n",
    "from pyecharts.charts import Map\n",
    "from pyecharts.faker import Faker\n",
    "\n",
    "c = (\n",
    "    Map()\n",
    "    .add(\"上海\", [list(z) for z in zip(上海地区,上海岗位个数)], \"上海\")\n",
    "    .set_global_opts(\n",
    "        title_opts=opts.TitleOpts(title=\"Map-上海地图\"), visualmap_opts = opts.VisualMapOpts()\n",
    "    )\n",
    "    .render( key+\"_dq_map_地区分布_\"+output_time+\".html\")\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98b45a96",
   "metadata": {},
   "source": [
    "# 职位分布"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "50ae3d93",
   "metadata": {},
   "outputs": [],
   "source": [
    " df_PM_sh['job.title']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "00638c91",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 还要合并回去原来的行\n",
    "df_PM_sh['job.title'][   df_PM_sh['job.title'].str.contains('（')   ].str.split('（').apply(lambda x:x[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6e0338be",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 处理过一些，清洗后的数据\n",
    "df_job_title = df_PM_sh['job.title'].apply(lambda x:x.split('（')[0].split('/')[0].split('(')[0]).value_counts()\n",
    "df_job_title"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "da260749",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_job_title.index.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a5c1bf8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(df_job_title.index.tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a15e8de",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_job_title.values.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "819099e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 未处理字符串的数据（不太整洁和干净的数据）\n",
    "df_PM_sh['job.title'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3b668756",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 列表推导式\n",
    "PM_title_words = [(  df_job_title.index.tolist()[i]   ,   df_job_title.values.tolist()[i]  )    for i in range(1,len(df_job_title.index.tolist())) ]\n",
    "PM_title_words"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "74842416",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyecharts import options as opts\n",
    "from pyecharts.charts import WordCloud\n",
    "from pyecharts.globals import SymbolType\n",
    "\n",
    "c = (\n",
    "    WordCloud()\n",
    "    .add(\"\", PM_title_words, word_size_range=[20, 100], shape=SymbolType.DIAMOND)\n",
    "    .set_global_opts(title_opts=opts.TitleOpts(title=\"WordCloud-shape-diamond\"))\n",
    "    .render( key +\"_wordcloud_map_岗位名称_\"+ output_time+\".html\")\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "776cfb4a",
   "metadata": {},
   "source": [
    "# job.labels"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2284a60b",
   "metadata": {},
   "source": [
    "统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e42147cb",
   "metadata": {},
   "outputs": [],
   "source": [
    " df_PM_sh['job.labels']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0562b0f9",
   "metadata": {},
   "outputs": [],
   "source": [
    " df_PM_sh['job.labels'].value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f975450a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_PM_sh['job.labels'].apply(lambda x:eval(x)).tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eb15997f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 列表的推导式\n",
    "PM_labels_list = [j     for i in df_PM_sh['job.labels'].apply(lambda x:eval(x)).tolist()       for j in i    ]\n",
    "PM_labels_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1f5d1372",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建words\n",
    "PM_labels_words = [ (i,PM_labels_list.count(i)) for i in set(PM_labels_list)]\n",
    "PM_labels_words"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5610ddbf",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 可视化词云图\n",
    "from pyecharts import options as opts\n",
    "from pyecharts.charts import WordCloud\n",
    "from pyecharts.globals import SymbolType\n",
    "\n",
    "\n",
    "c = (\n",
    "    WordCloud()\n",
    "    .add(\"\", PM_labels_words, word_size_range=[20, 100], shape=SymbolType.DIAMOND)\n",
    "    .set_global_opts(title_opts=opts.TitleOpts(title=\"WordCloud-shape-diamond\"))\n",
    "    .render(key +\"_wordcloud_map_职位标签_\"+ output_time+\".html\")\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "163882ac",
   "metadata": {},
   "source": [
    "# 平均薪资"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7466992",
   "metadata": {},
   "outputs": [],
   "source": [
    "# columns 重命名\n",
    "df_PM_sh = df_PM_sh.rename(columns={\n",
    "    'job.labels':'职位标签',\n",
    "    'job.refreshTime':'职位更新时间',\n",
    "    'job.title':'职位',\n",
    "    'job.salary':'薪资',\n",
    "    'job.dq':'地区',\n",
    "    'job.topJob':'是否top职位',\n",
    "    'job.requireWorkYears':'工作年限',\n",
    "    'job.requireEduLevel':'学历',\n",
    "    'comp.compStage':'公司融资情况',\n",
    "    'comp.compName':'公司名称',\n",
    "    'comp.compIndustry':'行业',\n",
    "    'comp.compScale':'规模'\n",
    "})\n",
    "df_PM_sh"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "70b828aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "非薪资面议 = df_PM_sh [ ~df_PM_sh['薪资'].str.contains(\"面议|元/天\")]\n",
    "非薪资面议"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "39a1fa9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "非薪资面议_detail = 非薪资面议['薪资'].apply(lambda x:x.split('薪')[0].split('·')).tolist()\n",
    "非薪资面议_detail"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d0d8e7f",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(平均薪资)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca370969",
   "metadata": {},
   "outputs": [],
   "source": [
    "非薪资面议['平均薪资']=平均薪资"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "80df9685",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 分地区平均薪资\n",
    "分地区_平均薪资 = 非薪资面议.groupby('地区').agg({'平均薪资':'median'})\n",
    "分地区_平均薪资"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fbc468d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "分地区_平均薪资_values =  [round(i[0],1) for i in 分地区_平均薪资.values.tolist()]\n",
    "分地区_平均薪资_values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5edf2afc",
   "metadata": {},
   "outputs": [],
   "source": [
    "分地区_平均薪资_index = 分地区_平均薪资.index.tolist()\n",
    "分地区_平均薪资_index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf713f8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyecharts import options as opts\n",
    "from pyecharts.charts import Bar\n",
    "from pyecharts.faker import Faker\n",
    "\n",
    "\n",
    "c = (\n",
    "    Bar()\n",
    "    .add_xaxis([i.split('-')[1] for i in 分地区_平均薪资_index[1:]])\n",
    "    .add_yaxis(\"地区\",分地区_平均薪资_values[1:])\n",
    "    .set_global_opts(\n",
    "        title_opts=opts.TitleOpts(title=\"PM-分地区-中位数薪资\"),\n",
    "        brush_opts=opts.BrushOpts(),\n",
    "    )\n",
    "    .render( key + \"_bar_with_brush_地区薪资中位数_\"+output_time+'.html')\n",
    ")\n",
    "# c.render_notebook()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a64b8899",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_year_salary = 非薪资面议.groupby('工作年限').agg({'平均薪资':'mean'})\n",
    "df_year_salary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65b2e77c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 分工作时间和学历平均薪资\n",
    "df_year_edulevel =  非薪资面议.groupby(['工作年限','学历']).agg({'平均薪资':'mean'})\n",
    "df_year_edulevel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c88d0517",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 分行业\n",
    "df_industry = 非薪资面议.groupby('行业').agg({'平均薪资':'mean'})\n",
    "df_industry"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b9dd4f71",
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.ExcelWriter(key+'_'+output_time+'_.xlsx') as writer:  \n",
    "    df_year_salary.to_excel(writer, sheet_name='分工作年限平均薪资')\n",
    "    df_year_edulevel.to_excel(writer, sheet_name='分学历平均薪资')\n",
    "    df_industry.to_excel(writer, sheet_name='分行业平均薪资')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9df80789",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
